USE [OctoNetApps]
GO

/****** Object:  StoredProcedure [classified].[ClassifiedAds_Save]    Script Date: 05/25/2011 06:57:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--CREATE TYPE selectedAttributeValues AS TABLE
--(	
--	AttributeID bigint,
--	AttributeValue tinyint
--)

--CREATE TYPE selectedCustomAttributeValues AS TABLE
--(	
--	AttributeID bigint,
--	AttributeValueName nvarchar(250)
--)

--GO

CREATE PROCEDURE [classified].[ClassifiedAds_Save]
	@ResourceID bigint,
	@CategoryID bigint,
	@AdID bigint,
	@Title nvarchar(200),
	@ExpiryTime smalldatetime,
	@Searching bit,
	@TextFormat tinyint,
	@AdText nvarchar(max),
	@Latitude float,
	@Longitude float,
	@PriceType tinyint,
	@Price decimal(10, 2),
	@ContactPhone varchar(25),
	@ContactEmail varchar(50),
	@ContactUrl varchar(512),
	@UserID bigint,
	@UserComputerID bigint,
	@UserIP varbinary(16),
	@AttributeValues selectedAttributeValues readonly,
	@CustomAttributeValues selectedCustomAttributeValues readonly,
	@Status tinyint
AS
BEGIN

	SET NOCOUNT ON;
	
	BEGIN TRY
	
		BEGIN TRANSACTION TR
	IF (@CategoryID = 0)
	BEGIN
		SELECT @CategoryID = CategoryID FROM classified.ClassifiedAds WHERE ResourceID = @ResourceID AND AdID = @AdID
	END
	
	declare @loc geography = NULL
	IF (@Latitude <> 0 AND @Longitude <> 0)
	BEGIN
		SET @loc = geography::Point(@Latitude, @Longitude, 4326)
	END
	
	UPDATE classified.ClassifiedAds
	SET Title = @Title,
		ExpiryTime = @ExpiryTime,
		Searching = @Searching,
		TextFormat = @TextFormat,
		AdText = @AdText,
		Location = @loc,
		PriceType = @PriceType,
		Price = @Price,
		ContactPhone = @ContactPhone,
		ContactEmail = @ContactEmail,
		ContactUrl = @ContactUrl,
		UserID = @UserID,
		UserComputerID = @UserComputerID,
		UserIP = @UserIP,
		[Status] = @Status
	WHERE ResourceID = @ResourceID AND CategoryID = @CategoryID AND AdID = @AdID
	
	IF @@ROWCOUNT = 0
	BEGIN
		INSERT INTO [classified].[ClassifiedAds]
           ([ResourceID]
           ,[AdID]
           ,[CategoryID]
           ,[PostTime]
           ,[ExpiryTime]
           ,[Searching]
           ,[Title]
           ,[AdText]
           ,[TextFormat]
           ,[Location]
           ,[PriceType]
           ,[Price]
           ,[ContactPhone]
           ,[ContactEmail]
           ,[ContactUrl]
           ,[UserID]
           ,[UserComputerID]
           ,[UserIP]
           ,[Status]
           ,[Description])
     VALUES
     (
           @ResourceID,
           @AdID,
           @CategoryID,
           CURRENT_TIMESTAMP,
           @ExpiryTime,
           @Searching,
           @Title,
           @AdText,
           @TextFormat,
           @loc,
           @PriceType,
           @Price,
           @ContactPhone,
           @ContactEmail, 
           @ContactUrl,
           @UserID,
           @UserComputerID, 
           @UserIP,
           @Status,
           NULL
     )

  END
  
  -- attribute values
  
  DELETE FROM classified.ClassifiedAdsAttributeValues
  WHERE ResourceID = @ResourceID AND AdID = @AdID;
  
  MERGE INTO classified.ClassifiedAdsAttributeValues caav
	USING @AttributeValues av ON (caav.AttributeValue = av.AttributeValue AND caav.AttributeID = av.AttributeID 
									AND caav.AdID = @AdID AND caav.ResourceID = @ResourceID)
	WHEN MATCHED THEN
	UPDATE SET caav.AttributeValue = av.AttributeValue--impossible
	WHEN NOT MATCHED THEN
	INSERT (ResourceID, AdID, AttributeID, CategoryID, AttributeValue)
	VALUES (@ResourceID, @AdID, av.AttributeID, @CategoryID, av.AttributeValue);
  
  -- custom attribute values
  
  DELETE FROM classified.ClassifiedAdsCustomAttributeValues
  WHERE ResourceID = @ResourceID AND AdID = @AdID;
  
  MERGE INTO classified.ClassifiedAdsCustomAttributeValues ccav
	USING @CustomAttributeValues cav ON (ccav.ResourceID = @ResourceID AND ccav.AdID = @AdID AND ccav.AttributeID = cav.AttributeID)
	WHEN MATCHED THEN
	UPDATE SET ccav.AttributeValueName = cav.AttributeValueName -- impossible
	WHEN NOT MATCHED THEN
	INSERT (ResourceID, CategoryID, AdID, AttributeID, AttributeValueName)
	VALUES (@ResourceID, @CategoryID, @AdID, cav.AttributeID, cav.AttributeValueName);
	
	COMMIT TRANSACTION TR
    END TRY
    BEGIN CATCH
		ROLLBACK TRANSACTION TR
    END CATCH
 
END





GO


